﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Text;
using ImageLib.Data.Database;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using System.IO;

namespace ImageLib.Data
{
    public class DataModel
    {
        private const string relativeDatabasePath = @"Database\ImgLib.sdf";

        private const string defaultConnFormatString = @"Data Source={0}; Password=Svnug2011";
        private string _connString;

        /// <summary>
        /// Initializes a new instance of the DataModel class.
        /// </summary>
        public DataModel()
            : this(GetConnString())
        { }

        /// <summary>
        /// Initializes a new instance of the DataModel class.
        /// </summary>
        /// <param name="connString"></param>
        public DataModel(string connString)
        {
            _connString = connString;
        }

        private static string GetConnString()
        {
            return string.Format(defaultConnFormatString, Path.Combine(Environment.CurrentDirectory, relativeDatabasePath));
        }

        private ImgLib GetImgLib()
        {
            return new ImgLib(_connString);
        }

        private SqlCeConnection GetCeConnection()
        {
            return new SqlCeConnection(_connString);
        }

        private Guid ExecuteInsertCommand(SqlCeCommand cmd, Guid id)
        {
            int rowsAffected;

            using (var conn = GetCeConnection())
            {
                try
                {
                    conn.Open();
                }
                catch (Exception ex)
                {
                    throw;
                }
                cmd.Connection = conn;

                try
                {
                    rowsAffected = cmd.ExecuteNonQuery();
                    return rowsAffected == 1 ? id : Guid.Empty;
                }
                catch (Exception ex)
                {
                    throw;
                }
            }

            return Guid.Empty;

        }


        #region Library methods

        public IQueryable<Library> GetLibraries()
        {
            ImgLib db = GetImgLib();

            return db.Library;
        }

        public Library GetLibrary(Guid id)
        {
            ImgLib db = GetImgLib();

            return db.Library.Where((lib) => lib.Id == id).SingleOrDefault();
        }

        public Library GetLibrary(string path)
        {
            ImgLib db = GetImgLib();

            return db.Library.Where((lib) => lib.Path == path).SingleOrDefault();
        }

        public Guid AddLibrary(string name, string path)
        {
            Library existingLibrary = GetLibrary(path);
            if (existingLibrary != null) { return existingLibrary.Id; }

            Guid id = Guid.NewGuid();
            var cmd = LibraryHelper.CreateInsertCommand(id, name, path);

            return ExecuteInsertCommand(cmd, id);
        }

        #endregion

        #region Image methods

        public IQueryable<Image> GetImages(Guid libraryId)
        {
            ImgLib db = GetImgLib();

            return db.Image.Where((img) => img.LibraryId == libraryId);
        }

        public IQueryable<Image> GetImages(Guid libraryId, string subFolder)
        {
            IQueryable<Image> images = GetImages(libraryId);

            return images.Where((img) => img.Path == subFolder);
        }

        public Guid AddImage(string name, string path, string extension, DateTime createDate, Guid libraryId)
        {
            Guid id = Guid.NewGuid();
            var cmd = ImageHelper.CreateInsertCommand(id, name, path, extension, createDate, libraryId);

            return ExecuteInsertCommand(cmd, id);
        }

        public IQueryable<string> GetSubFolders(Guid libraryId)
        {
            return GetImages(libraryId).Select((img) => img.Path).Distinct();
        }

        #endregion

        #region Field methods

        public IQueryable<Field> GetFields()
        {
            ImgLib db = GetImgLib();

            return db.Field;
        }

        public Field GetField(Guid id)
        {
            ImgLib db = GetImgLib();

            return db.Field.Where((fld) => fld.Id == id).SingleOrDefault();
        }

        public Guid AddField(string name)
        {
            Guid id = Guid.NewGuid();
            var cmd = FieldHelper.CreateInsertCommand(id, name);

            return ExecuteInsertCommand(cmd, id);
        }

        #endregion

        #region Tag methods

        public IQueryable<Tag> GetTags()
        {
            ImgLib db = GetImgLib();

            return db.Tag;
        }

        public IQueryable<Tag> GetTags(Guid fieldId)
        {
            ImgLib db = GetImgLib();

            return db.Tag.Where((tag) => tag.FieldId == fieldId);
        }

        public Tag GetTag(Guid id)
        {
            ImgLib db = GetImgLib();

            return db.Tag.Where((tag) => tag.Id == id).SingleOrDefault();
        }

        public Tag GetTag(Guid fieldId, string tagValue)
        {
            ImgLib db = GetImgLib();

            return db.Tag.Where((tag) => tag.FieldId == fieldId && string.Compare(tag.Value, tagValue, true) == 0).SingleOrDefault();
        }

        public Guid AddTag(Guid fieldId, string value)
        {
            Guid id = Guid.NewGuid();
            var cmd = TagHelper.CreateInsertCommand(id, fieldId, value);

            return ExecuteInsertCommand(cmd, id);
        }

        public ImageTag GetImageTag(Guid imageId, Guid tagId)
        {
            ImgLib db = GetImgLib();

            ImageTag existingTagAssoc = db.ImageTag.Where((imgTag) => imgTag.ImageId == imageId && imgTag.TagId == tagId).SingleOrDefault();

            if (existingTagAssoc != null) { return existingTagAssoc; }

            return null;
        }

        public Guid AddTagAssoc(Guid imageId, Guid tagId)
        {
            ImageTag existingTagAssoc = GetImageTag(imageId, tagId);

            if (existingTagAssoc != null) { return existingTagAssoc.Id; }

            Guid id = Guid.NewGuid();
            var cmd = TagAssocHelper.CreateInsertCommand(id, imageId, tagId);

            return ExecuteInsertCommand(cmd, id);
        }

        public Guid AddTagAssoc(Guid imageId, Guid fldId, string tagValue)
        {            
            Tag existingTag = GetTag(fldId, tagValue);

            Guid tagId = existingTag == null ? AddTag(fldId, tagValue) : existingTag.Id;

            return AddTagAssoc(imageId, tagId);
        }
        

        #endregion


        #region Helper Classes
        
        private static class ParmBuilder
        {
            public static SqlCeParameter IdParm(Guid value)
            {
                SqlCeParameter result = new SqlCeParameter("@id", SqlDbType.UniqueIdentifier);

                result.Value = value;

                return result;
            }

            public static SqlCeParameter IdParm(string key, Guid value)
            {
                SqlCeParameter result = new SqlCeParameter(key, SqlDbType.UniqueIdentifier);

                result.Value = value;

                return result;
            }

            public static SqlCeParameter NameParm(string value)
            {
                SqlCeParameter result = new SqlCeParameter("@name", SqlDbType.NVarChar, 250);

                result.Value = value;

                return result;
            }

            public static SqlCeParameter PathParm(string value)
            {
                SqlCeParameter result = new SqlCeParameter("@path", SqlDbType.NVarChar, 2000);

                result.Value = value;

                return result;
            }

            public static SqlCeParameter ExtensionParm(string value)
            {
                SqlCeParameter result = new SqlCeParameter("@extension", SqlDbType.NVarChar, 100);

                result.Value = value;

                return result;
            }

            public static SqlCeParameter DateParm(DateTime value)
            {
                SqlCeParameter result = new SqlCeParameter("@date", SqlDbType.DateTime);

                result.Value = value;

                return result;
            }

            public static SqlCeParameter ValueParm(string value)
            {
                SqlCeParameter result = new SqlCeParameter("@value", SqlDbType.NVarChar, 2000);

                result.Value = value;

                return result;
            }
        }

        private static class LibraryHelper
        {

            public static SqlCeCommand CreateInsertCommand(Guid id, string name, string path)
            {
                var cmd = GetInsertCommand();

                cmd.Parameters.Add(ParmBuilder.IdParm(id));
                cmd.Parameters.Add(ParmBuilder.NameParm(name));
                cmd.Parameters.Add(ParmBuilder.PathParm(path));

                return cmd;
            }

            private static SqlCeCommand GetInsertCommand()
            {
                return new SqlCeCommand(@"INSERT INTO Library VALUES(@id, @name, @path)");
            }

            
        }

        private static class ImageHelper
        {

            public static SqlCeCommand CreateInsertCommand(Guid id, string name, string path, string extension, DateTime createDate, Guid libraryId)
            {
                var cmd = GetInsertCommand();

                cmd.Parameters.Add(ParmBuilder.IdParm(id));
                cmd.Parameters.Add(ParmBuilder.NameParm(name));
                cmd.Parameters.Add(ParmBuilder.PathParm(path));
                cmd.Parameters.Add(ParmBuilder.ExtensionParm(extension));
                cmd.Parameters.Add(ParmBuilder.DateParm(createDate));
                cmd.Parameters.Add(ParmBuilder.IdParm("@libraryId", libraryId));

                return cmd;
            }

            private static SqlCeCommand GetInsertCommand()
            {
                return new SqlCeCommand(@"INSERT INTO Image VALUES(@id, @name, @path, @extension, @date, @libraryId)");
            }


        }

        private static class FieldHelper
        {

            public static SqlCeCommand CreateInsertCommand(Guid id, string name)
            {
                var cmd = GetInsertCommand();

                cmd.Parameters.Add(ParmBuilder.IdParm(id));
                cmd.Parameters.Add(ParmBuilder.NameParm(name));                

                return cmd;
            }

            private static SqlCeCommand GetInsertCommand()
            {
                return new SqlCeCommand(@"INSERT INTO Field VALUES(@id, @name)");
            }


        }

        private static class TagHelper
        {

            public static SqlCeCommand CreateInsertCommand(Guid id, Guid fieldId, string value)
            {
                var cmd = GetInsertCommand();

                cmd.Parameters.Add(ParmBuilder.IdParm(id));
                cmd.Parameters.Add(ParmBuilder.IdParm("@fieldId", fieldId));
                cmd.Parameters.Add(ParmBuilder.ValueParm(value));

                return cmd;
            }

            private static SqlCeCommand GetInsertCommand()
            {
                return new SqlCeCommand(@"INSERT INTO Tag VALUES(@id, @fieldId, @value)");
            }


        }

        private static class TagAssocHelper
        {

            public static SqlCeCommand CreateInsertCommand(Guid id, Guid imageId, Guid tagId)
            {
                var cmd = GetInsertCommand();

                cmd.Parameters.Add(ParmBuilder.IdParm(id));
                cmd.Parameters.Add(ParmBuilder.IdParm("@imageId", imageId));
                cmd.Parameters.Add(ParmBuilder.IdParm("@tagId", tagId));

                return cmd;
            }

            private static SqlCeCommand GetInsertCommand()
            {
                return new SqlCeCommand(@"INSERT INTO ImageTag VALUES(@id, @imageId, @tagId)");
            }


        }
        #endregion

    }

}
